﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Imports System.Windows.Forms
Imports FRAMEWORK.Framework.PermissionData

Public NotInheritable Class SQLConnections
    Public Enum ActionType As Integer
        [Select] = 0
        Insert = 1
        Update = 2
    End Enum

    Public Shared Cmd As System.Data.SqlClient.SqlCommand
    Public Shared Cnn As System.Data.SqlClient.SqlConnection
    Public Shared Tran As System.Data.SqlClient.SqlTransaction

    Public Shared ConnectionString As String = Configuration.ConfigurationManager.AppSettings("ConnectionString")
    Public Shared ConnectionString1 As String = Configuration.ConfigurationManager.AppSettings("ConnectionString1")

    Public Shared Sub SqlConnectionOpen()
        If SQLConnections.Cnn Is Nothing Then SQLConnections.Cnn = New System.Data.SqlClient.SqlConnection
        With SQLConnections.Cnn
            'If .State = ConnectionState.Closed Then
            '    .ConnectionString = SQLConnections.ConnectionString
            '    .Open()
            'End If
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = SQLConnections.ConnectionString
            .Open()
        End With
    End Sub
    Public Shared Sub SqlConnectionOpen1()
        If SQLConnections.Cnn Is Nothing Then SQLConnections.Cnn = New System.Data.SqlClient.SqlConnection
        With SQLConnections.Cnn
            'If .State = ConnectionState.Closed Then
            '    .ConnectionString = SQLConnections.ConnectionString
            '    .Open()
            'End If
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = SQLConnections.ConnectionString1
            .Open()
        End With
    End Sub

    Public Shared Sub SqlBeginTransaction()
        If SQLConnections.Cnn Is Nothing Then SQLConnections.Cnn = New System.Data.SqlClient.SqlConnection
        With SQLConnections.Cnn
            If .State = ConnectionState.Closed Then
                .ConnectionString = SQLConnections.ConnectionString
                .Open()
                SQLConnections.Tran = .BeginTransaction
            End If
        End With
    End Sub

    Public Shared Function SqlConnectionOpen(ByVal cnn As System.Data.SqlClient.SqlConnection) As System.Data.SqlClient.SqlConnection
        If cnn Is Nothing Then cnn = New System.Data.SqlClient.SqlConnection
        With cnn
            If .State = ConnectionState.Closed Then
                .ConnectionString = ""
                .Open()
            End If
        End With
        Return cnn
    End Function

    Public Shared Function SqlBeginTransaction(ByVal cnn As System.Data.SqlClient.SqlConnection) As System.Data.SqlClient.SqlConnection
        If cnn Is Nothing Then cnn = New System.Data.SqlClient.SqlConnection
        With cnn
            If .State = ConnectionState.Closed Then
                .ConnectionString = ""
                .Open()
                SQLConnections.Tran = .BeginTransaction
            End If
        End With
        Return cnn
    End Function

    Public Shared Function SqlBeginTransaction(ByVal cnn As System.Data.SqlClient.SqlConnection, ByVal tran As System.Data.SqlClient.SqlTransaction) As System.Data.SqlClient.SqlConnection
        If cnn Is Nothing Then cnn = New System.Data.SqlClient.SqlConnection
        With cnn
            If .State = ConnectionState.Closed Then
                .ConnectionString = ""
                .Open()
                tran = .BeginTransaction
            End If
        End With
        Return cnn
    End Function

    Public Shared Sub DisposeSqlConnection(ByVal cnn As System.Data.SqlClient.SqlConnection)
        If Not cnn Is Nothing Then
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            cnn.Dispose()
        End If
    End Sub

    Public Shared Sub DisposeSqlConnection(ByVal cmd As System.Data.SqlClient.SqlCommand)
        If Not cmd Is Nothing Then
            If Not cmd.Connection Is Nothing Then
                If cmd.Connection.State = ConnectionState.Open Then
                    cmd.Connection.Close()
                End If
                cmd.Connection.Dispose()
            End If
            cmd.Dispose()
        End If
    End Sub

    Public Shared Sub DisposeSqlConnection(ByVal adapter As System.Data.SqlClient.SqlDataAdapter)
        If Not adapter Is Nothing Then
            If Not adapter.SelectCommand Is Nothing Then
                If Not adapter.SelectCommand.Connection Is Nothing Then
                    If Not adapter.SelectCommand.Connection.State = ConnectionState.Open Then
                        adapter.SelectCommand.Connection.Close()
                    End If
                    adapter.SelectCommand.Connection.Dispose()
                End If
                adapter.SelectCommand.Dispose()
            End If
            adapter.Dispose()
        End If
    End Sub

    Public Shared Sub DisposeSqlTransaction(ByVal tran As System.Data.SqlClient.SqlTransaction)
        If Not tran Is Nothing Then
            If Not tran.Connection Is Nothing Then
                If tran.Connection.State = ConnectionState.Open Then
                    tran.Connection.Close()
                End If
                tran.Connection.Dispose()
            End If
            tran.Dispose()
        End If
    End Sub

    Public Shared Sub ClearParameterObject(ByVal cmd As System.Data.SqlClient.SqlCommand)
        If cmd.Parameters.Count > 0 Then
            cmd.Parameters.Clear()
        End If
    End Sub

    Public Shared Sub ClearParameterObject(ByVal type As ActionType, ByVal adapter As System.Data.SqlClient.SqlDataAdapter)
        Select Case type
            Case ActionType.Select
                If adapter.SelectCommand.Parameters.Count > 0 Then
                    adapter.SelectCommand.Parameters.Clear()
                End If
            Case ActionType.Insert
                If adapter.InsertCommand.Parameters.Count > 0 Then
                    adapter.InsertCommand.Parameters.Clear()
                End If
            Case ActionType.Update
                If adapter.UpdateCommand.Parameters.Count > 0 Then
                    adapter.UpdateCommand.Parameters.Clear()
                End If
        End Select
    End Sub

    Public Shared Function FunctionSelect(ByVal Store As String, ByVal ParameterName() As String, ByVal ParameterType() As System.Data.SqlDbType, ByVal ParameterNumber() As Integer, ByVal ParameterValue() As Object) As DataTable
        Dim L_Data As New DataTable
        Try
            SQLConnections.SqlConnectionOpen()
            SQLConnections.Cmd = SQLConnections.Cnn.CreateCommand

            With New SqlDataAdapter(SQLConnections.Cmd)
                .SelectCommand.CommandType = CommandType.StoredProcedure
                For i = 0 To F_Index - 1
                    .SelectCommand.Parameters.Add(ParameterName(i), ParameterType(i), ParameterNumber(i)).Value = ParameterValue(i)
                Next
                .SelectCommand.CommandText = Store
                .Fill(L_Data)
                .Dispose()
            End With
            L_Data.Dispose()
            SQLConnections.DisposeSqlConnection(SQLConnections.Cmd)
            Return L_Data

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return L_Data
        End Try
    End Function

    Public Shared Function FunctionSelect() As DataTable
        Return FunctionSelect(F_StoreName, F_Para_Name, F_SqlDbType, F_Para_Number, F_Para_Value)
    End Function
    Public Shared Function FunctionSelect1(ByVal Store As String, ByVal ParameterName() As String, ByVal ParameterType() As System.Data.SqlDbType, ByVal ParameterNumber() As Integer, ByVal ParameterValue() As Object) As DataTable
        Dim L_Data As New DataTable
        Try
            SQLConnections.SqlConnectionOpen1()
            SQLConnections.Cmd = SQLConnections.Cnn.CreateCommand

            With New SqlDataAdapter(SQLConnections.Cmd)
                .SelectCommand.CommandType = CommandType.StoredProcedure
                For i = 0 To F_Index - 1
                    .SelectCommand.Parameters.Add(ParameterName(i), ParameterType(i), ParameterNumber(i)).Value = ParameterValue(i)
                Next
                .SelectCommand.CommandText = Store
                .Fill(L_Data)
                .Dispose()
            End With
            L_Data.Dispose()
            SQLConnections.DisposeSqlConnection(SQLConnections.Cmd)
            Return L_Data

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return L_Data
        End Try
    End Function

    Public Shared Function FunctionSelect(ByVal P As Integer) As DataTable
        Return FunctionSelect1(F_StoreName, F_Para_Name, F_SqlDbType, F_Para_Number, F_Para_Value)
    End Function

    Public Shared Sub ClearParameters()
        For i = 0 To F_Index
            F_Para_Name(i) = Nothing
            F_SqlDbType(i) = Nothing
            F_Para_Number(i) = Nothing
            F_Para_Value(i) = Nothing
        Next
        F_Index = 0
        F_StoreName = ""
        F_DataTable = Nothing
    End Sub

    Public Shared Sub Parameters(ByVal ParamArray Array() As Object)
        F_Para_Name(F_Index) = Array(0)
        F_SqlDbType(F_Index) = Array(1)
        F_Para_Number(F_Index) = Array(2)
        F_Para_Value(F_Index) = Array(3)
        F_Index += 1
    End Sub

    Public Shared Sub ClearArrayListView()
        For i = 0 To F_ArrayNameToListView_Str.Length - 1
            F_ArrayNameToListView_Str(i) = Nothing
            F_ArraySizeToListView_Int(i) = Nothing
        Next
    End Sub

    Public Shared Sub ClearArrayComboBox()
        For i = 0 To F_ArrayNameToComboBox_Str.Length - 1
            F_ArrayNameToComboBox_Str(i) = Nothing
        Next
    End Sub

    Public Shared Sub ClearArrayData()
        For i = 0 To F_ArrayData_Str.Length - 1
            F_ArrayData_Str(i) = Nothing
        Next
    End Sub
End Class